#!/usr/bin/env python
# encoding: utf-8
"""
#-------------------------------------------------------------------#
#                   CONFIDENTIAL --- CUSTOM STUDIOS                 #     
#-------------------------------------------------------------------#
#                                                                   #
#                   @Project Name : Globallawonline                #
#                                                                   #
#                   @File Name    : NoPdfSqlClean.py                      #
#                                                                   #
#                   @Programmer   : 李建                            #
#                                                                   #  
#                   @Start Date   : 2021/1/19 15:04                 #
#                                                                   #
#                   @Last Update  : 2021/1/19 15:04                 #
#                                                                   #
#-------------------------------------------------------------------#
# Classes:该类用于清理无pdf文件的Sql记录                                                          #
#                                                                   #
#-------------------------------------------------------------------#
"""
import datetime
import os
import re

import pymysql
import redis

from settings import CJSQLCON, REDISCON, FILES_DIR, CJSQLTABLE, REDIS_KEY


class NoPdfSqlClean:

    def __init__(self):
        self.sqlconn = pymysql.connect(host=CJSQLCON['HOST'], port=CJSQLCON['PORT'], db=CJSQLCON['DB'], user=CJSQLCON['USER'], passwd=CJSQLCON['PASSWD'], charset='utf8')
        self.redisconn = redis.ConnectionPool(host=REDISCON['HOST'], port=REDISCON['PORT'], password=REDISCON['PASSWD'], db=REDISCON['DB'], decode_responses=True)

    def close(self):
        self.sqlconn.close()
        self.redisconn.disconnect()

    def sqlclean(self):
        print(str(datetime.datetime.now()) + "：开始进行无pdf文件的sql记录清洗")
        for table in CJSQLTABLE:
            sysid = []
            urlcursor = self.sqlconn.cursor()
            sql = "SELECT ID,FileUrl,DownLoadUrl,SYSID,SortA FROM %s ORDER BY SortA" % table
            print(str(datetime.datetime.now()) + "：" + sql)
            try:
                urlcursor.execute(sql)
                rs = urlcursor.fetchall()
                for r in rs:
                    r_l = r[1]
                    res_tr = r'''(?<=/PDF).*'''  # 从数据库记录中获取PDF路径的正则表达式
                    re_tr = re.findall(res_tr, r_l, re.S)  # 利用正则表达式获取PDF路径并存到链表
                    re_a = re_tr[0]  # 取链表的第一个值
                    pdf_path = FILES_DIR + re_a
                    state = os.path.exists(pdf_path)
                    # 查看pdf是否存在
                    if not state:
                        with open('nopdf.txt', 'a', encoding='utf-8') as f:
                            f.write(str(datetime.datetime.now()) + ":"  + str(r[0]) + ', ' + r[1] + ', ' + r[2] + ', ' + r[3] + ', ' + r[4] + '\n')
                        sysid.append(r[3])
            except Exception as e:
                print('%s：mysql查找数据异常：' % str(datetime.datetime.now()) + str(e))
            urlcursor.close()
            # 创建删除数据游标
            urlcursor1 = self.sqlconn.cursor()
            for sys in sysid:
                rediscursor = redis.Redis(connection_pool=self.redisconn)
                redis_key = REDIS_KEY[table]
                # sqll = "DELETE FROM %s WHERE SYSID = '%s'" % (table, sys)
                sqll = '''update %s set SYS_FLD_MARKSTATE = "2" where sysid = "%s"''' % (table, sys)
                try:
                    urlcursor1.execute(sqll)
                    try:
                        rediscursor.srem(redis_key, sys)
                        self.sqlconn.commit()
                    except Exception as e:
                        print('Redis删除数据异常：' + str(e))
                except Exception as e:
                    print('%s：mysql删除数据异常：' % str(datetime.datetime.now()) + str(e))
            urlcursor1.close()
        self.close()
        print(str(datetime.datetime.now()) + "：无pdf文件的sql记录清洗结束")
        return True
